import 'dart:math';

import 'package:flutter/material.dart';
import 'package:flutter_account_book/utils/db/entities/account_record.dart';
import 'package:intl/intl.dart';

import '../db_helper.dart';

class AccountRecordDao extends DbDao<AccountRecord> {
  AccountRecordDao({required super.db});
  @override
  String get tableName => "account_records";

  Future<List<AccountRecord>> flowAll() async {
    List list = await db.rawQuery(
        'SELECT * FROM $tableName WHERE deleted = 0 ORDER BY recordTime desc');
    return list.map((item) => AccountRecord.fromJson(item)).toList();
  }

  Future<List<AccountRecord>> flowByBookId(int bookId) async {
    List list = await db.rawQuery(
        'SELECT * FROM $tableName WHERE deleted = 0 AND (`books` & ?) > 0 ORDER BY recordTime desc',
        [bookId]);
    return list.map((item) => AccountRecord.fromJson(item)).toList();
  }

  Future<AccountRecord?> getById(String id) async {
    var result =
        await db.rawQuery('select * from $tableName where id = ?', [id]);
    if (result.isNotEmpty) {
      return AccountRecord.fromJson(result[0]);
    }
    return null;
  }

  Future<double> getTotalIncome() async {
    var result = await db
        .rawQuery('select SUM(amount) sum from $tableName where isIncome = 1');
    if (result.isNotEmpty) {
      return result[0]['sum'] as double? ?? 0;
    }
    return 0;
  }

  Future<double> getTotalExpend() async {
    var result = await db
        .rawQuery('select SUM(amount) sum from $tableName where isIncome = 0');
    if (result.isNotEmpty) {
      return result[0]['sum'] as double? ?? 0;
    }
    return 0;
  }

  Future<double> getTotalCurMonthIncome() async {
    var result = await db.rawQuery(
        "SELECT SUM(amount) AS total_amount FROM $tableName WHERE isIncome = 1 AND strftime('%Y-%m', datetime(recordTime / 1000, 'unixepoch')) = strftime('%Y-%m', 'now')");
    if (result.isNotEmpty) {
      return result[0]['total_amount'] as double? ?? 0;
    }
    return 0;
  }

  Future<double> getTotalCurMonthExpend() async {
    var result = await db.rawQuery(
        "SELECT SUM(amount) AS total_amount FROM $tableName WHERE isIncome = 0 AND strftime('%Y-%m', datetime(recordTime / 1000, 'unixepoch')) = strftime('%Y-%m', 'now')");
    if (result.isNotEmpty) {
      return result[0]['total_amount'] as double? ?? 0;
    }
    return 0;
  }

  Future<double> getTotalIncomeByBookId(int bookId) async {
    var result = await db.rawQuery(
        'select SUM(amount) sum from $tableName where isIncome = 1 AND (`books` & ?) > 0');
    if (result.isNotEmpty) {
      return result[0]['sum'] as double? ?? 0;
    }
    return 0;
  }

  Future<double> getTotalExpendByBookId(int bookId) async {
    var result = await db.rawQuery(
        'select SUM(amount) sum from $tableName where isIncome = 0 AND (`books` & ?) > 0');
    if (result.isNotEmpty) {
      return result[0]['sum'] as double? ?? 0;
    }
    return 0;
  }

  Future<double> getTotalCurMonthIncomeByBookId(int bookId) async {
    var result = await db.rawQuery(
        "SELECT SUM(amount) AS total_amount FROM $tableName WHERE isIncome = 1 AND (`books` & ?) > 0 AND strftime('%Y-%m', datetime(recordTime / 1000, 'unixepoch')) = strftime('%Y-%m', 'now')");
    if (result.isNotEmpty) {
      return result[0]['total_amount'] as double? ?? 0;
    }
    return 0;
  }

  Future<double> getTotalCurMonthExpendByBookId(int bookId) async {
    var result = await db.rawQuery(
        "SELECT SUM(amount) AS total_amount FROM $tableName WHERE isIncome = 0 AND (`books` & ?) > 0 AND strftime('%Y-%m', datetime(recordTime / 1000, 'unixepoch')) = strftime('%Y-%m', 'now')");
    if (result.isNotEmpty) {
      return result[0]['total_amount'] as double? ?? 0;
    }
    return 0;
  }

  Future<List<Map<String, Object?>>> getTotalIncomeAmountPerDayByRange(
      DateTimeRange range) async {
    var result = await db.rawQuery(
        "SELECT strftime('%Y-%m-%d', datetime(recordTime / 1000, 'unixepoch')) AS record_date, SUM(amount) AS daily_total FROM $tableName WHERE isIncome = 1 AND recordTime BETWEEN ? AND ? GROUP BY record_date ORDER BY record_date",
        [range.start.millisecondsSinceEpoch, range.end.millisecondsSinceEpoch]);
    return result;
  }

  Future<List<Map<String, Object?>>> getTotalIncomeAmountPerDayByBookIdAndRange(
      int bookId, DateTimeRange range) async {
    var result = await db.rawQuery(
        "SELECT strftime('%Y-%m-%d', datetime(recordTime / 1000, 'unixepoch')) AS record_date, SUM(amount) AS daily_total FROM $tableName WHERE isIncome = 1 AND (`books` & ?) > 0 AND recordTime BETWEEN ? AND ? GROUP BY record_date ORDER BY record_date",
        [
          bookId,
          range.start.millisecondsSinceEpoch,
          range.end.millisecondsSinceEpoch
        ]);
    return result;
  }

  Future<List<Map<String, Object?>>> getTotalExpendAmountPerDayByRange(
      DateTimeRange range) async {
    var result = await db.rawQuery(
        "SELECT strftime('%Y-%m-%d', datetime(recordTime / 1000, 'unixepoch')) AS record_date, SUM(amount) AS daily_total FROM $tableName WHERE isIncome = 0 AND recordTime BETWEEN ? AND ? GROUP BY record_date ORDER BY record_date",
        [range.start.millisecondsSinceEpoch, range.end.millisecondsSinceEpoch]);
    return result;
  }

  Future<List<Map<String, Object?>>> getTotalExpendAmountPerDayByBookIdAndRange(
      int bookId, DateTimeRange range) async {
    var result = await db.rawQuery(
        "SELECT strftime('%Y-%m-%d', datetime(recordTime / 1000, 'unixepoch')) AS record_date, SUM(amount) AS daily_total FROM $tableName WHERE isIncome = 0 AND (`books` & ?) > 0 AND recordTime BETWEEN ? AND ? GROUP BY record_date ORDER BY record_date",
        [
          bookId,
          range.start.millisecondsSinceEpoch,
          range.end.millisecondsSinceEpoch
        ]);
    return result;
  }

  Future<List<Map<String, Object?>>>
      getTotalIncomeRecordTypeAmountPerDayByRange(DateTimeRange range) async {
    var result = await db.rawQuery(
        "WITH RecordTotals AS ("
        "SELECT recordType, SUM(amount) AS totalAmount FROM account_records WHERE isIncome = 1 AND recordTime BETWEEN ? AND ? GROUP BY recordType),"
        "Top5RecordTypes AS ("
        "SELECT recordType FROM RecordTotals ORDER BY totalAmount DESC LIMIT 5 ),"
        "LabeledRecords AS ("
        "SELECT CASE WHEN recordType IN (SELECT recordType FROM Top5RecordTypes) THEN recordType ELSE '其他' END AS labeledType, amount FROM account_records WHERE isIncome = 1 AND recordTime BETWEEN ? AND ? )"
        "SELECT labeledType AS recordType, SUM(amount) AS totalAmount FROM LabeledRecords GROUP BY labeledType ORDER BY totalAmount DESC;",
        [
          range.start.millisecondsSinceEpoch,
          range.end.millisecondsSinceEpoch,
          range.start.millisecondsSinceEpoch,
          range.end.millisecondsSinceEpoch
        ]);
    return result;
  }

  Future<List<Map<String, Object?>>>
      getTotalIncomeRecordTypeAmountPerDayByByBookIdAndRange(
          int bookId, DateTimeRange range) async {
    var result = await db.rawQuery(
        "WITH RecordTotals AS ("
        "SELECT recordType, SUM(amount) AS totalAmount FROM account_records WHERE isIncome = 1 AND (`books` & ?) > 0 AND recordTime BETWEEN ? AND ? GROUP BY recordType),"
        "Top5RecordTypes AS ("
        "SELECT recordType FROM RecordTotals ORDER BY totalAmount DESC LIMIT 5 ),"
        "LabeledRecords AS ("
        "SELECT CASE WHEN recordType IN (SELECT recordType FROM Top5RecordTypes) THEN recordType ELSE '其他' END AS labeledType, amount FROM account_records WHERE isIncome = 1 AND (`books` & ?) > 0 AND recordTime BETWEEN ? AND ? )"
        "SELECT labeledType AS recordType, SUM(amount) AS totalAmount FROM LabeledRecords GROUP BY labeledType ORDER BY totalAmount DESC;",
        [
          bookId,
          range.start.millisecondsSinceEpoch,
          range.end.millisecondsSinceEpoch,
          bookId,
          range.start.millisecondsSinceEpoch,
          range.end.millisecondsSinceEpoch
        ]);
    return result;
  }

  Future<List<Map<String, Object?>>>
      getTotalExpendRecordTypeAmountPerDayByRange(DateTimeRange range) async {
    var result = await db.rawQuery(
        "WITH RecordTotals AS ("
        "SELECT recordType, SUM(amount) AS totalAmount FROM account_records WHERE isIncome = 0 AND recordTime BETWEEN ? AND ? GROUP BY recordType),"
        "Top5RecordTypes AS ("
        "SELECT recordType FROM RecordTotals ORDER BY totalAmount DESC LIMIT 5 ),"
        "LabeledRecords AS ("
        "SELECT CASE WHEN recordType IN (SELECT recordType FROM Top5RecordTypes) THEN recordType ELSE '其他' END AS labeledType, amount FROM account_records WHERE isIncome = 0 AND recordTime BETWEEN ? AND ? )"
        "SELECT labeledType AS recordType, SUM(amount) AS totalAmount FROM LabeledRecords GROUP BY labeledType ORDER BY totalAmount DESC;",
        [
          range.start.millisecondsSinceEpoch,
          range.end.millisecondsSinceEpoch,
          range.start.millisecondsSinceEpoch,
          range.end.millisecondsSinceEpoch
        ]);
    return result;
  }

  Future<List<Map<String, Object?>>>
      getTotalExpendRecordTypeAmountPerDayByByBookIdAndRange(
          int bookId, DateTimeRange range) async {
    var result = await db.rawQuery(
        "WITH RecordTotals AS ("
        "SELECT recordType, SUM(amount) AS totalAmount FROM account_records WHERE isIncome = 0 AND (`books` & ?) > 0 AND recordTime BETWEEN ? AND ? GROUP BY recordType),"
        "Top5RecordTypes AS ("
        "SELECT recordType FROM RecordTotals ORDER BY totalAmount DESC LIMIT 5 ),"
        "LabeledRecords AS ("
        "SELECT CASE WHEN recordType IN (SELECT recordType FROM Top5RecordTypes) THEN recordType ELSE '其他' END AS labeledType, amount FROM account_records WHERE isIncome = 0 AND (`books` & ?) > 0 AND recordTime BETWEEN ? AND ? )"
        "SELECT labeledType AS recordType, SUM(amount) AS totalAmount FROM LabeledRecords GROUP BY labeledType ORDER BY totalAmount DESC;",
        [
          bookId,
          range.start.millisecondsSinceEpoch,
          range.end.millisecondsSinceEpoch,
          bookId,
          range.start.millisecondsSinceEpoch,
          range.end.millisecondsSinceEpoch
        ]);
    return result;
  }

  Future<bool> has(String id) async {
    AccountRecord? bookshelf = await getById(id);
    return bookshelf != null;
  }

  Future<int> update(AccountRecord bookshelf) async {
    return await db.update(tableName, bookshelf.toJson(),
        where: 'id = ?', whereArgs: [bookshelf.id]);
  }

  Future<int> delete(AccountRecord book) async {
    return await db.delete(tableName, where: 'id = ?', whereArgs: [book.id]);
  }

  Future<String> getUnusedId() async {
    var idsS = DateFormat("yyyyMMddHHmmssS").format(DateTime.now());
    var randomNum = Random().nextInt(899) + 100;
    while ((await has("$idsS$randomNum"))) {
      randomNum = Random().nextInt(899) + 100;
    }

    return "$idsS$randomNum";
  }
}
